{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Time Series Prediction with BQML and AutoML\n",
    "\n",
    "**Objectives**\n",
    " 1. Learn how to use BQML to create a classification time-series model using `CREATE MODEL`.\n",
    " 2. Learn how to use BQML to create a linear regression time-series model.\n",
    " 3. Learn how to use AutoML Tables to build a time series model from data in BigQuery."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Set up environment variables and load necessary libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "PROJECT = \"your-gcp-project-here\" # REPLACE WITH YOUR PROJECT NAME\n",
    "REGION = \"us-central1\" # REPLACE WITH YOUR BUCKET REGION e.g. us-central1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%env\n",
    "PROJECT = PROJECT\n",
    "REGION = REGION"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create the dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from google.cloud import bigquery\n",
    "from IPython import get_ipython\n",
    "\n",
    "bq = bigquery.Client(project=PROJECT)\n",
    "\n",
    "\n",
    "def create_dataset():\n",
    "    dataset = bigquery.Dataset(bq.dataset(\"stock_market\"))\n",
    "    try:\n",
    "        bq.create_dataset(dataset)  # Will fail if dataset already exists.\n",
    "        print(\"Dataset created\")\n",
    "    except:\n",
    "        print(\"Dataset already exists\")\n",
    "\n",
    "\n",
    "def create_features_table():\n",
    "    error = None\n",
    "    try:\n",
    "        bq.query('''\n",
    "        CREATE TABLE stock_market.eps_percent_change_sp500\n",
    "        AS\n",
    "        SELECT *\n",
    "        FROM `stock_market.eps_percent_change_sp500`\n",
    "        ''').to_dataframe()\n",
    "    except Exception as e:\n",
    "        error = str(e)\n",
    "    if error is None:\n",
    "        print('Table created')\n",
    "    elif 'Already Exists' in error:\n",
    "        print('Table already exists.')\n",
    "    else:\n",
    "        raise Exception('Table was not created.')\n",
    "\n",
    "create_dataset()\n",
    "create_features_table()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Review the dataset\n",
    "\n",
    "In the previous lab we created the data, if you haven’t run the previous notebook, go back to [optional_1_data_exploration](../solutions/optional_1_data_exploration) and [optional_2_feature_engineering.ipynb](../solutions/optional_2_feature_engineering.ipynb) to create them. We will use modeling and saved them as tables in BigQuery.\n",
    "\n",
    "Let's examine that table again to see that everything is as we expect. Then, we will build a model using BigQuery ML using this table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --project $PROJECT\n",
    "#standardSQL\n",
    "SELECT\n",
    "  *\n",
    "FROM\n",
    "  stock_market.eps_percent_change_sp500\n",
    "LIMIT\n",
    "  10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Using BQML\n",
    "\n",
    "### Create classification model for `direction`\n",
    "\n",
    "To create a model\n",
    "1. Use `CREATE MODEL` and provide a destination table for resulting model. Alternatively we can use `CREATE OR REPLACE MODEL` which allows overwriting an existing model.\n",
    "2. Use `OPTIONS` to specify the model type (linear_reg or logistic_reg). There are many more options [we could specify](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-create#model_option_list), such as regularization and learning rate, but we'll accept the defaults.\n",
    "3. Provide the query which fetches the training data \n",
    "\n",
    "Have a look at [Step Two of this tutorial](https://cloud.google.com/bigquery/docs/bigqueryml-natality) to see another example.\n",
    "\n",
    "**The query will take about two minutes to complete**\n",
    "\n",
    "\n",
    "We'll start with creating a classification model to predict the `direction` of each stock. \n",
    "\n",
    "We'll take a random split using the `symbol` value. With about 500 different values, using `ABS(MOD(FARM_FINGERPRINT(symbol), 15)) = 1` will give 30 distinct `symbol` values which corresponds to about 171,000 training examples. After taking 70% for training, we will be building a model on about 110,000 training examples."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Lab Task #1a: Create model using BQML\n",
    "Use BQML's `CREATE OR REPLACE MODEL` to train a classification model which predicts the `direction` of a stock using the features in the `percent_change_sp500` table. Look at the [documentation for creating a BQML model](https://cloud.google.com/bigquery-ml/docs/bigqueryml-natality#step_two_create_your_model) to get the right syntax. Use `ABS(MOD(FARM_FINGERPRINT(symbol), 15)) = 1` to train on a subsample. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --project $PROJECT\n",
    "#standardSQL\n",
    "CREATE OR REPLACE MODEL\n",
    "  # TODO: Your code goes here\n",
    "  -- query to fetch training data\n",
    "SELECT\n",
    "  # TODO: Your code goes here\n",
    "FROM\n",
    "  `stock_market.eps_percent_change_sp500`\n",
    "WHERE\n",
    "  # TODO: Your code goes here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get training statistics and examine training info\n",
    "\n",
    "After creating our model, we can evaluate the performance using the [`ML.EVALUATE` function](https://cloud.google.com/bigquery-ml/docs/bigqueryml-natality#step_four_evaluate_your_model). With this command, we can find the precision, recall, accuracy F1-score and AUC of our classification model."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Lab Task #1b: Evaluate your BQML model.\n",
    "Use BQML's `EVALUATE` to evaluate the performance of your model on the validation set. Your query should be similar to [this example](https://cloud.google.com/bigquery-ml/docs/bigqueryml-natality#step_four_evaluate_your_model)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --project $PROJECT\n",
    "#standardSQL\n",
    "SELECT\n",
    "  *\n",
    "FROM\n",
    "  # TODO: Your code goes here.\n",
    "  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also examine the training statistics collected by Big Query. To view training results we use the [`ML.TRAINING_INFO`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-train) function."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Lab Task #1c: Examine the training information in BQML.\n",
    "Use BQML's `TRAINING_INFO` to see statistics of the training job executed above."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --project $PROJECT\n",
    "#standardSQL\n",
    "SELECT\n",
    "  *\n",
    "FROM\n",
    "  # TODO: Your code goes here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Compare to simple benchmark\n",
    "\n",
    "Another way to asses the performance of our model is to compare with a simple benchmark. We can do this by seeing what kind of accuracy we would get using the naive strategy of just predicted the majority class. For the training dataset, the majority class is 'STAY'. The following query we can see how this naive strategy would perform on the eval set."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --project $PROJECT\n",
    "#standardSQL\n",
    "WITH\n",
    "  eval_data AS (\n",
    "  SELECT\n",
    "    symbol,\n",
    "    Date,\n",
    "    Open,\n",
    "    close_MIN_prior_5_days,\n",
    "    close_MIN_prior_20_days,\n",
    "    close_MIN_prior_260_days,\n",
    "    close_MAX_prior_5_days,\n",
    "    close_MAX_prior_20_days,\n",
    "    close_MAX_prior_260_days,\n",
    "    close_AVG_prior_5_days,\n",
    "    close_AVG_prior_20_days,\n",
    "    close_AVG_prior_260_days,\n",
    "    close_STDDEV_prior_5_days,\n",
    "    close_STDDEV_prior_20_days,\n",
    "    close_STDDEV_prior_260_days,\n",
    "    direction\n",
    "  FROM\n",
    "    `stock_market.eps_percent_change_sp500`\n",
    "  WHERE\n",
    "    tomorrow_close IS NOT NULL\n",
    "    AND ABS(MOD(FARM_FINGERPRINT(symbol), 15)) = 1\n",
    "    AND ABS(MOD(FARM_FINGERPRINT(symbol), 15 * 100)) > 15 * 70\n",
    "    AND ABS(MOD(FARM_FINGERPRINT(symbol), 15 * 100)) <= 15 * 85)\n",
    "SELECT\n",
    "  direction,\n",
    "  (COUNT(direction)* 100 / (\n",
    "    SELECT\n",
    "      COUNT(*)\n",
    "    FROM\n",
    "      eval_data)) AS percentage\n",
    "FROM\n",
    "  eval_data\n",
    "GROUP BY\n",
    "  direction"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So, the naive strategy of just guessing the majority class would have accuracy of 0.5509 on the eval dataset, just below our BQML model. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create regression model for `normalized change`\n",
    "\n",
    "We can also use BigQuery to train a regression model to predict the normalized change for each stock. To do this in BigQuery we need only change the OPTIONS when calling `CREATE OR REPLACE MODEL`. This will give us a more precise prediction rather than just predicting if the stock will go up, down, or stay the same. Thus, we can treat this problem as either a regression problem or a classification problem, depending on the business needs.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Lab Task #2a: Create a regression model in BQML.\n",
    "Use BQML's `CREATE OR REPLACE MODEL` to train another model, this time a regression model, which predicts the `normalized_change` of a given stock based on the same features we used above. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --project $PROJECT\n",
    "#standardSQL\n",
    "CREATE OR REPLACE MODEL\n",
    "  # TODO: Your code goes here\n",
    "  -- query to fetch training data\n",
    "SELECT\n",
    "  # TODO: Your code goes here\n",
    "FROM\n",
    "  `stock_market.eps_percent_change_sp500`\n",
    "WHERE\n",
    "  # TODO: Your code goes here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Just as before we can examine the evaluation metrics for our regression model and examine the training statistics in Big Query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --project $PROJECT\n",
    "#standardSQL\n",
    "SELECT\n",
    "  *\n",
    "FROM\n",
    "  ML.EVALUATE(MODEL `stock_market.price_model`,\n",
    "    (\n",
    "    SELECT\n",
    "      symbol,\n",
    "      Date,\n",
    "      Open,\n",
    "      close_MIN_prior_5_days,\n",
    "      close_MIN_prior_20_days,\n",
    "      close_MIN_prior_260_days,\n",
    "      close_MAX_prior_5_days,\n",
    "      close_MAX_prior_20_days,\n",
    "      close_MAX_prior_260_days,\n",
    "      close_AVG_prior_5_days,\n",
    "      close_AVG_prior_20_days,\n",
    "      close_AVG_prior_260_days,\n",
    "      close_STDDEV_prior_5_days,\n",
    "      close_STDDEV_prior_20_days,\n",
    "      close_STDDEV_prior_260_days,\n",
    "      normalized_change\n",
    "    FROM\n",
    "      `stock_market.eps_percent_change_sp500`\n",
    "    WHERE\n",
    "      normalized_change IS NOT NULL\n",
    "      AND ABS(MOD(FARM_FINGERPRINT(symbol), 15)) = 1\n",
    "      AND ABS(MOD(FARM_FINGERPRINT(symbol), 15 * 100)) > 15 * 70\n",
    "      AND ABS(MOD(FARM_FINGERPRINT(symbol), 15 * 100)) <= 15 * 85))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --project $PROJECT\n",
    "#standardSQL\n",
    "SELECT\n",
    "  *\n",
    "FROM\n",
    "  ML.TRAINING_INFO(MODEL `stock_market.price_model`)\n",
    "ORDER BY iteration"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Lab Task #3: Create a model using AutoML. \n",
    "Follow the steps below to create a time series model using AutoML. Here we will walk through the steps to build a classification model to predict `direction` as above. You can also easily create a regression model as well. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Train a Time Series model using AutoML Tables\n",
    "\n",
    "### Step 1. Launch AutoML\n",
    "\n",
    "Within the GCP console, navigate to Tables in the console menu.\n",
    "\n",
    "<img src=\"../assets/console_menu_tables.png\" width='50%'>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Click **Enable API**, if API is not enabled.\n",
    "\n",
    "Click **GET STARTED**.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 2. Create a Dataset\n",
    "\n",
    "Select **New Dataset** and give it a name like `stock_market` and click *Create Dataset*. In the section on Importing data, select the option to import your data from a BigQuery Table. Fill in the details for your project, the dataset ID (stock_market), and the table ID (eps_percent_change_sp500).\n",
    "\n",
    "<img src='../assets/import_data_options.png' width='50%'>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 3. Import the Data\n",
    "\n",
    "Once you have created the dataset you can then import the data. This will take a few minutes.\n",
    "\n",
    "<img src='../assets/importing_data.png' width='50%'>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 4. Train the model \n",
    "\n",
    "Once the data has been imported into the dataset. You can examine the Schema of your data, Analyze the properties and values of the features and ultimately Train the model. Here you can also determine the label column and features for training the model. Since we are doing a classifcation model, we'll use `direction` as our target column.\n",
    "\n",
    "<img src='../assets/schema_analyze_train.png' width='80%'>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Under the `Train` tab, click **Train Model**. You can choose the features to use when training. Select the same features as we used above. The training will take upto 45-50 minutes to complete.\n",
    "\n",
    "<img src='../assets/train_model.png' width='50%'>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 5. Evaluate your model.\n",
    "\n",
    "Training can take many hours. But once training is complete you can inspect the evaluation metrics of your model. Since this is a classification task, we can also adjust the threshold and explore how different thresholds will affect your evaluation metrics. Also on that page, we can explore the feature importance of the various features used in the model and view confusion matrix for our model predictions.\n",
    "\n",
    "<img src='../assets/eval_metrics.png' width='80%'>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 6. Predict with the trained model. \n",
    "\n",
    "Once the model is done training, navigate to the Models page and Deploy the model, so we can test prediction. \n",
    "\n",
    "<img src='../assets/deploy_model.png' width='80%'>\n",
    "\n",
    "When calling predictions, you can call batch prediction jobs by specifying a BigQuery table or csv file. Or you can do online prediction for a single instance."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Copyright 2022 Google Inc. Licensed under the Apache License, Version 2.0 (the \"License\"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an \"AS IS\" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License"
   ]
  }
 ],
 "metadata": {
  "environment": {
   "name": "tf2-gpu.2-1.m68",
   "type": "gcloud",
   "uri": "gcr.io/deeplearning-platform-release/tf2-gpu.2-1:m68"
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
